﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;

namespace NB.MGJ.Service
{
    internal struct CacheKey
    {
        public const string GlobalDataAnalyse = "GlobalDataAnalyse_Global";
        public const string AntifakeAnalyseForPageOne = "AntifakeAnalyseForPageOne_{0}";
        public const string SearchTopTenForEnterprise = "SearchTopTenForEnterprise_{0}";
        public const string AntifleeingAnalyseForPageTwo = "AntifleeingAnalyseForPageTwo_{0}";
        public const string OutWarehouseTopSevenForEnterprise = "OutWarehouseTopSevenForEnterprise_{0}";
        public const string SearchPlaceTopTenForAntifake = "SearchPlaceTopTenForAntifake_{0}";
        public const string OriginsMaterialAnalyseForPageThree = "OriginsMaterialAnalyseForPageThree_{0}";
        public const string AwardTopFiveForActivity = "AwardTopFiveForActivity_{0}";
        public const string ActivityAnalyseForPageFour = "ActivityAnalyseForPageFour_{0}";
    }

    internal class CacheManager
    {
        private static T Get<T>(string key) where T : class
        {
            return System.Web.HttpRuntime.Cache.Get(key) as T;
        }

        private static void Set(string key, object data, TimeSpan timespan)
        {
            System.Web.HttpRuntime.Cache.Insert(key,
                data,
                null,
                System.Web.Caching.Cache.NoAbsoluteExpiration,
                timespan,

                System.Web.Caching.CacheItemPriority.Normal,
                null);
        }

        public static T Get<T>(string key, Func<T> _func, TimeSpan timespan) where T : class
        {
            var _result = Get<T>(key);
            if (_result == null)
            {
                _result = _func();
                Set(key, _result, timespan);
            }
            return _result;
        }
    }

    public class AntifakeResultModel
    {
        public Dictionary<string, List<long>> MonthList { get; set; }
        public long TotalWeChat { get; set; }
        public long TotalWeb { get; set; }
        public long TotalTel { get; set; }
        public long TotalSNS { get; set; }

        public object WeChatNormalPercent { get; set; }
        public object WebSiteNormalPercent { get; set; }
        public object TelNormalPercent { get; set; }
        public object SNSNormalPercent { get; set; }
    }

    public class GloablDataAnalyseModel
    {
        public long EnterpriseCount { get; set; }
        public long GenCoodeNums { get; set; }
        public long ScaningCodeCount { get; set; }
        public long TodayScanCodeCount { get; set; }
    }

    /// <summary>
    ///数据分析服务
    ///Lv
    ///2018/02/28
    /// </summary>
    public class SystemDataAnalyse
    {
        /// <summary>
        /// 总数据统计分析
        /// </summary>
        public static GloablDataAnalyseModel GlobalDataAnalyse()
        {
            return CacheManager.Get<GloablDataAnalyseModel>(CacheKey.GlobalDataAnalyse, () =>
              {
                  using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                  {
                      using (MySqlCommand cmd_o = conn.CreateCommand())
                      {
                          conn.Open();
                          cmd_o.CommandText = "SELECT COUNT(*) FROM organization_enterprise";
                          var EnterprsieCount = cmd_o.ExecuteScalar();
                          cmd_o.CommandText = "SELECT SUM(Gen_Code_Nums) GenCodeNums FROM codemanage_gencode";
                          var Gen_Code_Nums = cmd_o.ExecuteScalar();
                          var ScaningCodeCount = StatisticalTable("nblogmain", cmd_o);
                          var TodayScanCodeCount = StatisticalTodayScanCount(cmd_o);
                          return new GloablDataAnalyseModel
                          {
                              EnterpriseCount = Convert.ToInt64(EnterprsieCount),
                              GenCoodeNums = Convert.ToInt64(Gen_Code_Nums),
                              ScaningCodeCount = ScaningCodeCount,
                              TodayScanCodeCount = TodayScanCodeCount
                          };
                      }
                  }
              }, new TimeSpan(24, 0, 0));
        }

        #region 防伪

        #region 扫码量

        /// <summary>
        /// 统计指定机构每月扫码量
        /// </summary>
        private class ResultModel2
        {
            public long Count { get; set; }
            public int QueryType { get; set; }
        }

        public static AntifakeResultModel AntifakeAnalyseForPageOne(string eCode, int? sYear, int? eYear)
        {
            if (string.IsNullOrWhiteSpace(eCode)) { return null; }
            bool SearchByYear = false;
            SearchByYear = sYear.HasValue && eYear.HasValue;
            if (SearchByYear)
            {
                if (sYear == eYear)
                {
                    return AntifakeAnalyseForEnterprise_Deal(eCode, string.Format("{0}-01-01 00:00:00", sYear.Value), string.Format("{0}-12-31 23:59:59", eYear.Value));
                }

                List<ResultModel2> resultModel2 = new List<ResultModel2>();
                AntifakeResultModel resultModel = new AntifakeResultModel();
                resultModel.MonthList = new Dictionary<string, List<long>>
                {
                    {"WeChat",new List<long>{}},
                    {"WebSite",new List<long>{}},
                    {"SNS",new List<long>{}},
                    {"TEL",new List<long>{}}
                };
                using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd_s = conn.CreateCommand())
                    {
                        conn.Open();
                        for (int i = sYear.Value; i <= eYear.Value; i++)
                        {
                            string s_time = string.Format("{0}-01-01 00:00:00", i), e_time = string.Format("{0}-12-31 23:59:59", i);
                            cmd_s.CommandText = string.Format(@"SELECT
                                                     COUNT(*) Count,
                                                     QueryType
                                                     FROM
                                                      nblogmain.codelog{0}
                                                     WHERE
                                                     QueryTime BETWEEN '{1}'
                                                     AND '{2}'
                                                     GROUP BY QueryType", eCode, s_time, e_time);
                            MySqlDataReader Reader = cmd_s.ExecuteReader();
                            while (Reader.Read())
                            {
                                resultModel2.Add(new ResultModel2
                                {
                                    Count = Convert.ToInt64(Reader["Count"]),
                                    QueryType = Convert.ToInt32(Reader["QueryType"])
                                });
                            }
                            ResultModel2 _WeChat = resultModel2.Where(p => p.QueryType == 0).FirstOrDefault();
                            resultModel.MonthList["WeChat"].Add(_WeChat == null ? 0 : _WeChat.Count);
                            ResultModel2 _WebSite = resultModel2.Where(p => p.QueryType == 1).FirstOrDefault();
                            resultModel.MonthList["WebSite"].Add(_WebSite == null ? 0 : _WebSite.Count);
                            ResultModel2 _SNS = resultModel2.Where(p => p.QueryType == 2).FirstOrDefault();
                            resultModel.MonthList["SNS"].Add(_SNS == null ? 0 : _SNS.Count);
                            ResultModel2 _TEL = resultModel2.Where(p => p.QueryType == 3).FirstOrDefault();
                            resultModel.MonthList["TEL"].Add(_TEL == null ? 0 : _TEL.Count);
                            resultModel2.Clear();
                            Reader.Close();
                        }
                        return resultModel;
                    }
                }
            }
            return CacheManager.Get<AntifakeResultModel>(string.Format(CacheKey.AntifakeAnalyseForPageOne, eCode), () =>
            {
                string s_time = DateTime.Now.ToString("yyyy-01-01 00:00:00"), e_time = DateTime.Now.ToString("yyyy-12-31 23:59:59");

                return AntifakeAnalyseForEnterprise_Deal(eCode, s_time, e_time);
            }, new TimeSpan(12, 0, 0));
        }

        public class QueryModel2
        {
            public long WeChat { get; set; }
            public long WebSite { get; set; }
            public long SNS { get; set; }
            public long TEL { get; set; }
            public long NormalWeChat { get; set; }
            public long NormalWebSite { get; set; }
            public long NormalSNS { get; set; }
            public long NormalTEL { get; set; }
            public int Month { get; set; }
        }

        public static AntifakeResultModel AntifakeAnalyseForEnterprise_Deal(string eCode, string s_time, string e_time)
        {

            try
            {
                using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd_o = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd_o.CommandType = System.Data.CommandType.Text;
                        cmd_o.CommandText = string.Format(@"SELECT
                                 COUNT(IF(QueryType = 0,TRUE,NULL)) WeChat,
                                 COUNT(IF(QueryType = 1,TRUE,NULL)) WebSite,
                                 COUNT(IF(QueryType = 2,TRUE,NULL)) SNS,
                                 COUNT(IF(QueryType = 3,TRUE,NULL)) TEL,
                                 COUNT(if(QueryType = 0 AND Valid=1,TRUE,NULL)) AS NormalWeChat,
                                 COUNT(if(QueryType = 1 AND Valid=1,TRUE,NULL)) AS NormalWebSite,
                                 COUNT(if(QueryType = 2 AND Valid=1,TRUE,NULL)) AS NormalSNS,
                                 COUNT(if(QueryType = 3 AND Valid=1,TRUE,NULL)) AS NormalTEL,
                                 date_format(QueryTime, '%m') Month
                                 FROM
                                  nblogmain.codelog{0}
                                 WHERE
                                 	QueryTime BETWEEN '{1}'
                                 AND '{2}'
                                 GROUP BY DATE_FORMAT(QueryTime, '%m');
                                 ", eCode, s_time, e_time);

                        MySqlDataReader Reader = cmd_o.ExecuteReader();
                        List<QueryModel2> querymodel = new List<QueryModel2>();
                        while (Reader.Read())
                        {
                            querymodel.Add(new QueryModel2
                            {
                                WeChat = Convert.ToInt64(Reader["WeChat"]),
                                WebSite = Convert.ToInt64(Reader["WebSite"]),
                                SNS = Convert.ToInt64(Reader["SNS"]),
                                TEL = Convert.ToInt64(Reader["TEL"]),
                                NormalWeChat = Convert.ToInt64(Reader["NormalWeChat"]),
                                NormalWebSite = Convert.ToInt64(Reader["NormalWebSite"]),
                                NormalSNS = Convert.ToInt64(Reader["NormalSNS"]),
                                NormalTEL = Convert.ToInt64(Reader["NormalTEL"]),
                                Month = Convert.ToInt32(Reader["Month"])
                            });
                        }
                        for (int i = 1; i <= 12; i++)
                        {
                            if (querymodel.Where(p => p.Month == i).FirstOrDefault() == null)
                            {
                                querymodel.Insert((i - 1), new QueryModel2 { });
                            }
                        }
                        long _WebChat = querymodel.Sum(p => p.WeChat);
                        long _WebSite = querymodel.Sum(p => p.WebSite);
                        long _TEL = querymodel.Sum(p => p.TEL);
                        long _SNS = querymodel.Sum(p => p.SNS);

                        return new AntifakeResultModel
                        {
                            MonthList = new Dictionary<string, List<long>>
                    {
                        {"WeChat",querymodel.Select(p=>p.WeChat).ToList()},
                        {"WebSite",querymodel.Select(p=>p.WebSite).ToList()},
                        {"SNS",querymodel.Select(p=>p.SNS).ToList()},
                        {"TEL",querymodel.Select(p=>p.TEL).ToList()},
                    },
                            TotalWeChat = _WebChat,
                            TotalWeb = _WebSite,
                            TotalTel = _TEL,
                            TotalSNS = _SNS,
                            WeChatNormalPercent = _WebChat == 0 ? "0" : (querymodel.Sum(p => p.NormalWeChat) * 100 / (double)_WebChat).ToString("0"),
                            WebSiteNormalPercent = _WebSite == 0 ? "0" : (querymodel.Sum(p => p.NormalWebSite) * 100 / (double)_WebSite).ToString("0"),
                            TelNormalPercent = _TEL == 0 ? "0" : (querymodel.Sum(p => p.NormalTEL) * 100 / (double)_TEL).ToString("0"),
                            SNSNormalPercent = _SNS == 0 ? "0" : (querymodel.Sum(p => p.NormalSNS) * 100 / (double)_SNS).ToString("0")
                        };
                    }
                }
            }
            catch
            {
                return new AntifakeResultModel
                {
                    MonthList = new Dictionary<string, List<long>>
                    {
                        {"WeChat",new List<long>{}},
                        {"WebSite",new List<long>{}},
                        {"SNS",new List<long>{}},
                        {"TEL",new List<long>{}},
                    },
                    TotalWeChat = 0,
                    TotalWeb = 0,
                    TotalTel = 0,
                    TotalSNS = 0,
                    WeChatNormalPercent = "0",
                    WebSiteNormalPercent = "0",
                    TelNormalPercent = "0",
                    SNSNormalPercent = "0"
                };
            }
        }

        #endregion 扫码量

        #region 最近搜索TOP10

        public static List<object> SearchTopTenForAntifake(string eCode)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(eCode)) { return null; }

                return CacheManager.Get<List<object>>(string.Format(CacheKey.SearchTopTenForEnterprise, eCode), () =>
                {
                    MySqlConnection conn = new MySqlConnection(ConnectionString);
                    MySqlConnection conn2 = new MySqlConnection(ConnectionString);
                    MySqlCommand cmd_o = conn.CreateCommand();
                    MySqlCommand cmd_s = conn2.CreateCommand();
                    conn.Open();
                    conn2.Open();
                    cmd_o.CommandText = string.Format("SELECT Fw_SCode,Fw_Code,QueryType,QueryTime,QueryIp,Valid,QueryProvince,QueryCity FROM `nblogmain`.codelog{0} ORDER BY QueryTime DESC LIMIT 8", eCode);
                    MySqlDataReader Reader = cmd_o.ExecuteReader();
                    List<object> resultlist = new List<object>();
                    while (Reader.Read())
                    {
                        string fwscode = Reader["Fw_SCode"].ToString();
                        string fwcode = Reader["Fw_Code"].ToString();
                        Object placeName = null;
                        if (fwcode.Length > 8)
                        {
                            string tbName = string.Format("antifake_digitcode{0}", fwcode.Substring(0, 8));
                            cmd_s.CommandText = string.Format(@"SELECT
                        IF (IsCrossArea,	CONCAT_WS(',',JurisdictionPrivanceName,AcrossPrivanceName),
	                    JurisdictionPrivanceName
                          )
                        FROM
                      	antifleeing_distributor
                        WHERE
                      	ID = (
                      		SELECT
                      			DistributorID
                      		FROM
                      			antifleeing_outbounddetails
                      		WHERE
                      			WLcode = (SELECT Wl_Code FROM nbcodemain.{0} WHERE Fw_CodeSecret = '{1}')
                      		ORDER BY
                      			CreateDate DESC
                      		LIMIT 1
                      	)", tbName, fwscode);
                            placeName = cmd_s.ExecuteScalar();
                        }
                        resultlist.Add(new
                        {
                            Code = fwscode,
                            QueryType = Reader[2].ToString(),
                            QueryIp = Reader[4].ToString(),
                            QueryTime = Reader[3].ToString(),
                            Valid = Convert.ToInt32(Reader[5]),
                            PlaceForBelong = string.Format("{0}{1}", Reader[6].ToString(), Reader[7].ToString()),
                            Antifleeing = (placeName ?? "未知").ToString().Contains(Reader[6].ToString()) && !string.IsNullOrWhiteSpace(Reader[6].ToString()) ? "正常" : "可疑"
                        });
                    }
                    Reader.Close();
                    cmd_o.Dispose();
                    cmd_s.Dispose();
                    conn.Close();
                    conn2.Close();
                    return resultlist;
                }, new TimeSpan(12, 0, 0));
            }
            catch
            {
                return new List<object> { };
            }
        }

        #endregion 最近搜索TOP10

        #region 搜索地区TOP10

        /// <summary>
        /// 搜索地区排行TOP10
        /// </summary>
        public static List<object> SearchPlaceTopTenForAntifake(string eCode)
        {
            try
            {
                return CacheManager.Get<List<object>>(string.Format(CacheKey.SearchPlaceTopTenForAntifake, eCode), () =>
                {
                    if (string.IsNullOrWhiteSpace(eCode))
                    {
                        return null;
                    }
                    using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                    {
                        using (MySqlCommand cmd_o = conn.CreateCommand())
                        {
                            conn.Open();
                            List<object> ResultList = new List<object>();
                            string tbName = string.Format("codelog{0}", eCode);
                            cmd_o.CommandText = string.Format("SELECT COUNT(*) Count,QueryProvince FROM `nblogmain`.{0} GROUP BY QueryProvince ORDER BY  Count DESC", tbName);
                            MySqlDataReader Reader = cmd_o.ExecuteReader();
                            while (Reader.Read())
                            {
                                if (Reader["QueryProvince"].Equals(""))
                                {
                                    continue;
                                }
                                ResultList.Add(new
                                {
                                    name = Reader["QueryProvince"].ToString(),
                                    value = Convert.ToInt32(Reader["Count"])
                                });
                            }
                            Reader.Close();
                            return ResultList;
                        }
                    }
                }, new TimeSpan(12, 0, 0));
            }
            catch
            {
                return new List<object> { };
            }
        }

        #endregion 搜索地区TOP10

        #endregion 防伪

        #region 窜货

        #region 出入库退货

        /// <summary>
        ///出入库退货统计
        /// </summary>
        private static Dictionary<string, long[]> AntifleeingAnalyseForPageTwo_Deal(string eId, string sYear)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    Dictionary<string, long[]> antifleeing = new Dictionary<string, long[]>
                     {
                         {"InWarehouse",new long[12]},
                         {"OutWarehouse",new long[12]},
                         {"ReturnGoods",new long[12]}
                     };
                    conn.Open();
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = string.Format(@"SELECT
                                  DATE_FORMAT(CreateDate,'%m') Month,
                                  COUNT(IF(FormCode LIKE 'RK%',TRUE,NULL)) RKCount,
                                  COUNT(IF(FormCode LIKE 'CK%',TRUE,NULL)) CKCount
                                  FROM
                                  antifleeing_iowarehouse
                                  WHERE
                                  EnterpriseID = {0} AND (CreateDate BETWEEN '{1}-01-01 00:00:00' AND '{1}-12-31 23:59:59')
                                  GROUP BY DATE_FORMAT(CreateDate,'%y-%m')", eId, sYear);
                    MySqlDataReader Reader = cmd.ExecuteReader();
                    while (Reader.Read())
                    {
                        int Month = Convert.ToInt32(Reader["Month"]);
                        antifleeing["InWarehouse"][Month - 1] = Convert.ToInt64(Reader["RKCount"]);
                        antifleeing["OutWarehouse"][Month - 1] = Convert.ToInt64(Reader["CKCount"]);
                    }
                    Reader.Close();
                    cmd.CommandText = string.Format(@"SELECT
	                              COUNT(*) Count,
                                  DATE_FORMAT(CreateDate,'%m') Month
                                  FROM
	                              antifleeing_productreturn
                                  WHERE EnterpriseId = {0} AND (CreateDate BETWEEN '{1}-01-01 00:00:00' AND '{1}-12-31 23:59:59')
                                  GROUP BY DATE_FORMAT(CreateDate,'%y-%m')", eId, sYear);
                    Reader = cmd.ExecuteReader();
                    while (Reader.Read())
                    {
                        int Month = Convert.ToInt32(Reader["Month"]);
                        antifleeing["ReturnGoods"][Month - 1] = Convert.ToInt64(Reader["Count"]);
                    }
                    return antifleeing;
                }
            }
        }

        public static Dictionary<string, long[]> AntifleeingAnalyseForPageTwo(string eId, string sYear)
        {
            if (!string.IsNullOrWhiteSpace(sYear))
            {
                return AntifleeingAnalyseForPageTwo_Deal(eId, sYear);
            }
            return CacheManager.Get<Dictionary<string, long[]>>(string.Format(CacheKey.AntifleeingAnalyseForPageTwo, eId), () =>
            {
                sYear = sYear ?? DateTime.Now.Year.ToString();
                return AntifleeingAnalyseForPageTwo_Deal(eId, sYear);
            }, new TimeSpan(12, 0, 0));
        }

        #endregion 出入库退货

        /// <summary>
        /// 有问题  速度过慢  待修改
        /// Lv
        /// </summary>
        /// <param name="ECode"></param>
        //        public static void AntifleeingTopTenForAntifake(string ECode)
        //        {
        //            MySqlCommand cmd_o = Dequeue();
        //            int R = 0;
        //            cmd_o.CommandText = string.Format("SELECT COUNT(*) FROM `nblogmain`.codelog{0}", ECode);
        //            long Count = Convert.ToInt64(cmd_o.ExecuteScalar());
        //            Enqueue(cmd_o);
        //            List<Task<List<object>>> __Task = new List<Task<List<object>>>();
        //            Queue<int> __Result = new Queue<int>();
        //            while (R < Count)
        //            {
        //                __Result.Enqueue(R);
        //                R += 1000;
        //            }
        //            for (int i = 0; i < __Result.Count; i++)
        //            {
        //                __Task.Add(Task.Run<List<object>>(() =>
        //                {
        //                    using (MySqlConnection conn = new MySqlConnection(ConnectionString))
        //                    {
        //                        using (MySqlCommand cmd = conn.CreateCommand())
        //                        {
        //                            List<object> ResultList = new List<object>();
        //                            conn.Open();
        //                            cmd.CommandText = string.Format("SELECT Fw_SCode,Fw_Code,QueryProvince FROM `nblogmain`.codelog{0} LIMIT {1},1000", ECode, __Result.Dequeue());
        //                            MySqlDataReader Reader = cmd.ExecuteReader();
        //                            List<string> Results = new List<string>();
        //                            while (Reader.Read())
        //                            {
        //                                Results.Add(string.Format("{0},{1},{2}", Reader["Fw_SCode"].ToString(), Reader["Fw_Code"].ToString(), Reader["QueryProvince"].ToString()));
        //                            }
        //                            Reader.Close();
        //                            foreach (var item in Results)
        //                            {
        //                                var _Result = item.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
        //                                string FWCode = _Result[1];
        //                                string FWCode_SEC = _Result[0];
        //                                string _ProvinceName = _Result[2];
        //                                Object PlaceName = null;
        //                                if (FWCode.Length < 8) { continue; }
        //                                string TableName = string.Format("antifake_digitcode{0}", FWCode.Substring(0, 8));
        //                                //MySqlCommand cmd_a = Dequeue();
        //                                cmd.CommandText = string.Format(@"SELECT
        //                                                                  IF (IsCrossArea,	CONCAT_WS(',',JurisdictionPrivanceName,AcrossPrivanceName),
        //	                                                              JurisdictionPrivanceName
        //                                                                     )
        //                                                                  FROM
        //                                                                 	antifleeing_distributor
        //                                                                  WHERE
        //                      	                                          ID = (
        //                      	                                          	SELECT
        //                      	                                          		DistributorID
        //                      	                                          	FROM
        //                      	                                          		antifleeing_outbounddetails
        //                      	                                          	WHERE
        //                      	                                          		WLcode = (SELECT Wl_Code FROM nbcodemain.{0} WHERE Fw_CodeSecret = '{1}')
        //                      	                                          	ORDER BY
        //                      	                                          		CreateDate DESC
        //                      	                                          	LIMIT 1
        //                      	                                          )", TableName, FWCode_SEC);
        //                                PlaceName = cmd.ExecuteScalar();
        //                                //Enqueue(cmd_a);
        //                                ResultList.Add(new
        //                                {
        //                                    Code = FWCode_SEC,
        //                                    PlaceForBelong = string.Format("{0}", _ProvinceName),
        //                                    Antifleeing = (PlaceName ?? "未知").ToString().Contains(_ProvinceName) && !string.IsNullOrWhiteSpace(_ProvinceName) ? "正常" : "可疑"
        //                                });
        //                            }
        //                            return ResultList;
        //                        }
        //                    }
        //                }));
        //            }
        //            Task.WaitAll(__Task.ToArray());
        //            var rs = __Task;
        //        }

        #region 出库TOP7

        /// <summary>
        /// 排名出库TOP 7
        /// </summary>
        /// <param name="eId"></param>
        /// <returns></returns>
        public static Dictionary<string, long> OutWarehouseTopSevenForEnterprise(string eId)
        {
            return CacheManager.Get<Dictionary<string, long>>(string.Format(CacheKey.OutWarehouseTopSevenForEnterprise, eId), () =>
            {
                using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                {
                    using (MySqlCommand cmd_o = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd_o.CommandText = string.Format(
                           @"SELECT SUM(B.Num) Count,A.RealName FROM sys_user  A
                             LEFT JOIN antifleeing_iowarehouse B ON B.CreateLoginID = A.ID AND B.FormCode LIKE 'CK%'
                             WHERE   A.EnterpriseId = {0} AND A.Distributor!=0
                             GROUP BY CreateLoginID
                             ORDER BY Count DESC
                             LIMIT 7"
                        , eId);
                        MySqlDataReader Reader = cmd_o.ExecuteReader();
                        Dictionary<string, long> Result_Dic = new Dictionary<string, long>();
                        while (Reader.Read())
                        {
                            if ((Reader["RealName"] is DBNull) || (Reader["Count"] is DBNull))
                            {
                                continue;
                            }
                            Result_Dic.Add(Reader["RealName"].ToString(), Convert.ToInt64(Reader["Count"]));
                        }
                        return Result_Dic;
                    }
                }
            }, new TimeSpan(12, 0, 0));
        }

        #endregion 出库TOP7

        #endregion 窜货

        #region 溯源

        //Origins_Material

        public static long[] OriginsMaterialAnalyseForPageThree_Deal(string eId, string sYear)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    long[] Result = new long[12];
                    conn.Open();
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = string.Format(@"SELECT
                                      DATE_FORMAT(ChangeDate,'%m') `Month`,
                                      COUNT(*) Count
                                      FROM
                                      	origins_material
                                      WHERE
                                      	EnterpriseId = {0}
                                      AND (
                                      	ChangeDate BETWEEN '{1}-01-01 00:00:00'
                                      	AND '{1}-12-31 23:59:59'
                                      )
                                      GROUP BY
                                      	DATE_FORMAT(ChangeDate, '%y-%m')", eId, sYear ?? DateTime.Now.Year.ToString());
                    MySqlDataReader Reader = cmd.ExecuteReader();
                    while (Reader.Read())
                    {
                        int Month = Convert.ToInt32(Reader["Month"]);
                        Result[Month - 1] = Convert.ToInt64(Reader["Count"]);
                    }
                    return Result;
                }
            }
        }

        public static long[] OriginsMaterialAnalyseForPageThree(string eId, string sYear)
        {
            if (!string.IsNullOrWhiteSpace(sYear))
            {
                return OriginsMaterialAnalyseForPageThree_Deal(eId, sYear);
            }
            return CacheManager.Get<long[]>(string.Format(CacheKey.OriginsMaterialAnalyseForPageThree, eId), () =>
            {
                return OriginsMaterialAnalyseForPageThree_Deal(eId, sYear);
            }, new TimeSpan(12, 0, 0));
        }

        #endregion 溯源

        #region 活动

        private static Dictionary<string, long[]> ActivityAnalyseForPageFour_Deal(string eCode, string sYear, string sMonth)
        {
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand cmd = conn.CreateCommand())
                {
                    Dictionary<string, long[]> Result = new Dictionary<string, long[]>
                    {
                        {"ScanCount",new long[31]},
                        {"ReceiveCount",new long[31]}
                    };
                    conn.Open();
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = string.Format(@"SELECT
                              Count(*) TotalCount,
                              DATE_FORMAT(Time,'%d') Days,
                              COUNT(IF(Denomination > -1,TRUE,NULL)) ReceiveCount
                              FROM
                              `marketing`.marketing_thewinning{0}
                              WHERE
                              Time BETWEEN '{1}-{2}-01 00:00:00 '
                              AND '{1}-{2}-31 23:59:59'
                              GROUP BY
                              DATE_FORMAT(Time, '%y-%m-%d')", eCode, sYear ?? DateTime.Now.Year.ToString(), sMonth ?? DateTime.Now.Month.ToString());
                    MySqlDataReader Reader = cmd.ExecuteReader();
                    while (Reader.Read())
                    {
                        int Day = Convert.ToInt32(Reader["Days"]);
                        Result["ScanCount"][Day - 1] = Convert.ToInt64(Reader["TotalCount"]);
                        Result["ReceiveCount"][Day - 1] = Convert.ToInt64(Reader["ReceiveCount"]); ;
                    }
                    return Result;
                }
            }
        }

        public static Dictionary<string, long[]> ActivityAnalyseForPageFour(string eCode, string sYear, string sMonth)
        {
            try
            {
                if (!string.IsNullOrWhiteSpace(sYear) && !string.IsNullOrWhiteSpace(sMonth))
                {
                    return ActivityAnalyseForPageFour_Deal(eCode, sYear, sMonth);
                }
                return CacheManager.Get<Dictionary<string, long[]>>(string.Format(CacheKey.ActivityAnalyseForPageFour, eCode), () =>
                 {
                     return ActivityAnalyseForPageFour_Deal(eCode, sYear, sMonth);
                 }, new TimeSpan(12, 0, 0));
            }
            catch
            {
                return new Dictionary<string, long[]>
                    {
                        {"ScanCount",new long[31]},
                        {"ReceiveCount",new long[31]}
                    };
            }
        }

        public static List<object> AwardTopFiveForActivity(string eCode)
        {
            try
            {
                return CacheManager.Get<List<object>>(string.Format(CacheKey.AwardTopFiveForActivity, eCode), () =>
                {
                    using (MySqlConnection conn = new MySqlConnection(ConnectionString))
                    {
                        using (MySqlCommand cmd_o = conn.CreateCommand())
                        {
                            conn.Open();
                            cmd_o.CommandText = string.Format(@"SELECT * FROM
                                (SELECT D.`Name`,A.JiaMiCode, A.Denomination,B.HeadImgUrl,B.Nickname,C.Title,B.OA_Time,B.Phone FROM marketing.marketing_thewinning{0} A
                                LEFT JOIN marketing.marketing_user{0} B ON B.ID = A.UserID
                                LEFT JOIN marketing_activity C ON C.ID = A.ActivityID
                                LEFT JOIN marketing_awards D ON D.ID = A.AwardsID) TemTable
                                WHERE (TemTable.`Name` != ''OR TemTable.`Name` != NULL) AND TemTable.Denomination > -1
                                ORDER BY TemTable.OA_Time DESC LIMIT 5", eCode);
                            List<object> Result = new List<object>();
                            MySqlDataReader Reader = cmd_o.ExecuteReader();
                            while (Reader.Read())
                            {
                                Result.Add(new
                                {
                                    ActivityTitle = Reader["Title"],
                                    AwardName = Reader["Name"],
                                    Code = Reader["JiaMiCode"],
                                    ImagUrl = Reader["HeadImgUrl"],
                                    NickName = Reader["NickName"],
                                    Time = Reader["OA_Time"],
                                    Phone = Reader["Phone"],
                                    Denomination = Reader["Denomination"]
                                });
                            }
                            Reader.Close();
                            return Result;
                        }
                    }
                }, new TimeSpan(12, 0, 0));
            }
            catch
            {
                return new List<object>();
            }
        }

        #endregion 活动

        #region 初始

        private static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
            }
        }

        private static Queue<MySqlCommand> mySqlCommand;

        private SystemDataAnalyse()
        {
        }

        #endregion 初始

        #region 其它

        /// <summary>
        /// 计算指定库中所有表的行数估计值
        /// Lv
        /// 2018/03/01
        /// </summary>
        private static long StatisticalTable(string tbname, MySqlCommand cmd_o)
        {
            if (string.IsNullOrWhiteSpace(tbname))
            {
                return 0;
            }
            try
            {
                cmd_o.CommandText = string.Format("SELECT SUM(TABLE_ROWS) FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '{0}'", tbname);
                var Result = cmd_o.ExecuteScalar();
                if (Result != null)
                {
                    return Convert.ToInt64(Result);
                }
            }
            catch
            {
            }
            return 0;
        }

        /// <summary>
        /// 计算今日扫码量
        /// </summary>
        private static long StatisticalTodayScanCount(MySqlCommand cmd_o)
        {
            long ScanCount = 0;
            cmd_o.CommandText = "SHOW TABLES FROM nblogmain";
            MySqlDataReader mReader = cmd_o.ExecuteReader();
            if (!mReader.HasRows) { return ScanCount; }
            List<object> listTbName = new List<object>();
            while (mReader.Read())
            {
                listTbName.Add(mReader[0]);
            }
            mReader.Close();
            if (listTbName.Count == 0)
            {
                return ScanCount;
            }
            var date_s = DateTime.Now.Date;
            var date_e = date_s.AddDays(1);
            foreach (var item in listTbName)
            {
                if (item == null) { continue; }
                cmd_o.CommandText = string.Format("SELECT COUNT(*) from `nblogmain`.{0} WHERE QueryTime >= '{1}' AND QueryTime < '{2}'", item, date_s, date_e);
                ScanCount += Convert.ToInt64(cmd_o.ExecuteScalar());
            }
            return ScanCount;
        }

        #endregion 其它
    }
}